﻿package work;

import java.sql.Connection;

import org.apache.log4j.Logger;

import work.utils.Oracle;

public class OracleCountHByear {

	Logger log = Logger.getLogger(OraclelCount.class);

	Oracle oracle = new Oracle();

	Connection con = null;

	String class_code = "HB";

	String year_before = "1997-1-1";

	String year_after = "2007-1-1";

	//
	// String query = "SELECT id,sid,name FROM std";
	//
	// String querytest = "select count(*) as c from st_standard ";
	//
	// String insert = "insert into std (sid,name,tcrq) values ('gb
	// 2003','国家标准',sysdate())";
	//
	// String update = "update std set gm=1";
	//
	// String delete = "delete from test where name='j'";

	private void OracleQuery() {

		String[] class_three_code = { "DL", "FZ", "QB", "QC", "HG", "JC", "JB",
				"SB", "SY", "YS", "YB", "MT", "NY", "LY", "LS", "HJ", "SC",
				"SJ", "SL", "YD", "YY", "GH", "WS", "GA", "SN", "JJF", "JJG",
				"JC", "JG", "JGJ", "CJ", "CJJ", "JTJ", "CECS", "DB11", "GB",
				"JT" };
		
		String in_str="";
		
		select a.class_three_code,b.class_name_cn,count(1) from st_standard a,st_class b where a.class_three_code = b.class_code and b.class_layer =3  and a.class_two_code in('HB','DB') and a.text_address is not null group by a.class_three_code,b.class_name_cn;
		
		
		String[] class_three_code_name = { "电力", "纺织", "轻工", "汽车", "化工", "建材",
				"机械", "商业", "石油天然气", "有色金属", "黑色冶金", "煤炭", "农业", "林业", "粮食",
				"环境", "水产", "电子", "水利", "通讯", "医药", "供销", "卫生", "公共安全", "商检",
				"定量包装商品净含量计量检验规则", "国家计量规范标准", "建材行业标准", "建筑工业行业标准",
				"建筑工程行业标准", "城镇建设行业标准", "城镇建设工程行业标准", "交通行业工程建设技术规范",
				"中国工程建设标准化协会标准", "北京地方标准", "国标", "交通行业" };

		HashMap<String, String[]> map = new HashMap();

		HashMap map_25 = new HashMap();
		map_25.put("DL", "电力");
		map_25.put("NY", "农业");
		map_25.put("SC", "水产");
		map_25.put("LY", "林业");
		map_25.put("QB", "轻工");

		map_25.put("SB", "商业");
		map_25.put("LS", "粮食");
		map_25.put("WS", "卫生");
		map_25.put("HG", "化工");
		map_25.put("JC", "建材");

		map_25.put("FZ", "纺织");
		map_25.put("JB", "机械");
		map_25.put("QC", "汽车");
		map_25.put("SY", "石油天然气");
		map_25.put("SJ", "电子");

		map_25.put("YD", "通讯");
		map_25.put("YY", "医药");
		map_25.put("HJ", "环境");
		map_25.put("GA", "公共安全");
		map_25.put("SN", "商检");

		map_25.put("JG", "建筑工业");
		map_25.put("YB", "黑色金属");
		map_25.put("YS", "有色金属");
		map_25.put("SL", "水利");
		map_25.put("GX", "供销");

		// Set set_25 = map_25.keySet();// 获取全部键
		// Iterator iterater_25 = set_25.iterator();


		Object[] map_key_25 = map_25.keySet().toArray();
		Arrays.sort(map_key_25);
		String map_key_str_25 = null;
		

		// GB题录数	
		// select a.class_two_code,count(1) from st_standard a where a.class_two_code = 'GB' group by a.class_two_code;
		// GB文本数
		// select a.class_two_code,count(1) from st_standard a where a.class_two_code = 'GB' and a.text_address is not null group by a.class_two_code;
		// 地标、行标题录数
		// select a.class_three_code,b.class_name_cn,count(1) from st_standard a,st_class b where a.class_three_code = b.class_code and b.class_layer =3  and a.class_two_code in('HB','DB') group by a.class_three_code,b.class_name_cn;
		// 地标、行标文本数
		// select a.class_three_code,b.class_name_cn,count(1) from st_standard a,st_class b where a.class_three_code = b.class_code and b.class_layer =3  and a.class_two_code in('HB','DB') and a.text_address is not null group by a.class_three_code,b.class_name_cn;

		
		
		String ST_CLASS_HB = "select t.class_code as code,t.class_name_cn as name from st_class t where t.class_layer='3' and t.father_class_code='"+class_code+"'";

		try {

			Statement stmt_ST_CLASS_HB = null;
			ResultSet rs_ST_CLASS_HB = null;
			con = oracle.getConnection(con);
			stmt_ST_CLASS_HB = con.createStatement();

			// 记录结果
			long s1 = 0;
			long s2 = 0;
			long s3 = 0;
			long s4 = 0;
			long s5 = 0;

			// =================
			// 查询分类表
			if (stmt_ST_CLASS_HB.execute(ST_CLASS_HB)) {

				rs_ST_CLASS_HB = stmt_ST_CLASS_HB.getResultSet();

				while (rs_ST_CLASS_HB.next()) {

					String[] temp = new String[5];

					//

					// 每次查询都是不同的查询语句，所以要每次都要 建立 Statement,ResultSet
					Statement stmt1 = con.createStatement();
					Statement stmt2 = con.createStatement();
					Statement stmt3 = con.createStatement();
					Statement stmt4 = con.createStatement();

					ResultSet rs1 = null;
					ResultSet rs2 = null;
					ResultSet rs3 = null;
					ResultSet rs4 = null;

					String class_code = rs_ST_CLASS_HB.getString("code");
					String class_name_cn = rs_ST_CLASS_HB.getString("name");

					// log.info("class_code :" + class_code);
					// log.info("class_name_cn :" + class_name_cn);

					// 查询各个分类的标准
					// 标准题录总数
					String q1 = "select count(*)  as c from st_standard t where t.class_three_code='"
							+ class_code + "'";
					// 近 10 年标准题录总数
					String q2 = "select count(*)  as c from st_standard t where t.class_three_code='"
							+ class_code
							+ "'"
							+ " and t.act_time between to_date('"
							+ year_before
							+ "','yyyy-mm-dd') and to_date('"
							+ year_after
							+ "','yyyy-mm-dd')";

					// 标准文本总数
					String q3 = "select count(*)  as c from st_standard t where t.class_three_code='"
							+ class_code
							+ "'"
							+ " and t.text_address is not null";
					// 近十年标准文本总数
					String q4 = "select count(*)  as c from st_standard t where t.class_three_code='"
							+ class_code
							+ "'"
							+ "  and t.text_address is not null and t.act_time between to_date('"
							+ year_before
							+ "','yyyy-mm-dd') and to_date('"
							+ year_after + "','yyyy-mm-dd')";

					// 查询 q1
					if (stmt1.execute(q1)) {
						rs1 = stmt1.getResultSet();
						while (rs1.next()) {
							s1 = rs1.getLong("c");
						}
					}

					// 查询 q2
					if (stmt2.execute(q2)) {
						rs2 = stmt2.getResultSet();
						while (rs2.next()) {
							s2 = rs2.getLong("c");
						}
					}

					// 查询 q3
					if (stmt3.execute(q3)) {
						rs3 = stmt3.getResultSet();
						while (rs3.next()) {
							s3 = rs3.getLong("c");
						}
					}

					// 查询 q4
					if (stmt4.execute(q4)) {
						rs4 = stmt4.getResultSet();
						while (rs4.next()) {
							s4 = rs4.getLong("c");
						}
					}

					temp[0] = new Long(s1).toString();
					temp[1] = new Long(s2).toString();
					temp[2] = new Long(s3).toString();
					temp[3] = new Long(s4).toString();
					temp[4] = class_name_cn; // 保存行业名称

					map.put(class_code, temp);

					// 分类循环，每次查询之后，都要关闭
					oracle.closeDB(rs1, stmt1);
					oracle.closeDB(rs2, stmt2);
					oracle.closeDB(rs3, stmt4);
					oracle.closeDB(rs4, stmt4);

				}// while 分类表循环完毕
				oracle.closeDB(rs_ST_CLASS_HB, stmt_ST_CLASS_HB);
			}// if 查询分类表

			// 关闭连接
			oracle.closeDB(con);

			// 打印测试

			// Object key = null;
			// Object value = null;
			String key = null;
			String[] value = null;

			// 先处理 25 个重点行业
			
			if(class_code =="HB"){//重点行业，国标不区分
				
				for (int i = 0; i < map_key_25.length; i++) {
					map_key_str_25 = (String) map_key_25[i];

					try {
						value = (String[]) map.get(map_key_str_25);
						map.remove(map_key_str_25);
						System.out.println(value[4] + ":" + " \t 总题录数："
								+ value[0] + " \t 近 10 年标准题录总数：" + value[1]
								+ "\t 标准文本总数：" + value[2] + "\t近十年标准文本总数： "
								+ value[3] + "\t");
						System.out.print("\n");

					} catch (Exception e) {
						log.info(key + " is not in map");
						e.printStackTrace();
					}
				}

				System.out.println("===============");
				
			}
			
			
			// 处理剩余的行业
			Object[] map_key = map.keySet().toArray();
			Arrays.sort(map_key);//按照标准类别代码排序
			String map_key_str = null;

			for (int i = 0; i < map_key.length; i++) {
				map_key_str = (String) map_key[i];
				value = (String[]) map.get(map_key_str);
				System.out.println(value[4] + ":" + " \t 总题录数：" + value[0]
						+ " \t 近 10 年标准题录总数：" + value[1] + "\t 标准文本总数："
						+ value[2] + "\t近十年标准文本总数： " + value[3] + "\t");
				System.out.print("\n");
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
			log.info("exception in query action.");

			// oracle.closeDB(rs1, stmt1);
			// oracle.closeDB(rs2, stmt2);
			// oracle.closeDB(rs3, stmt4);
			// oracle.closeDB(rs4, stmt4);
			oracle.closeDB(con);
		}

		// }
	}

	public static void main(String[] args) {

		OraclelCount test = new OraclelCount();
		test.OracleQuery();

		double f = 469 * 0.23 + 492 * 0.8 + 142 * 0 + 451 * 0.88 + 17 * 0.52
				+ 127 * 0.25 + 121 * 0.9;
		double f2 = 323 * 0.21 + 240 * 0.9 + 90 * 0 + 282 * 0.87 + 129 * 0.8
				+ 72 * 0.15 + 113 * 0.85;
		// System.out.print("f=" + f + " f2=" + f2);

	}

}
